﻿Imports System.Data
Imports System.Data.SqlClient
Imports CondorTravel.BE
Imports CondorTravelUTIL.DL

Public Class InformeCampoDALC

    Public Function InsertarInformeCampo(ByVal objInformeCampoBE As InformeCampoBE, ByRef bExito As Boolean, ByRef sMensaje As String) As Boolean

        Dim arrSqlParameter(9) As SqlParameter

        Try
            arrSqlParameter(0) = New SqlParameter()
            arrSqlParameter(0).ParameterName = "@SUPERVISOR"
            arrSqlParameter(0).SqlDbType = SqlDbType.VarChar
            arrSqlParameter(0).Size = 50
            arrSqlParameter(0).Direction = ParameterDirection.Input
            arrSqlParameter(0).Value = objInformeCampoBE.Supervisor

            arrSqlParameter(1) = New SqlParameter()
            arrSqlParameter(1).ParameterName = "@COD_CIUDAD"
            arrSqlParameter(1).SqlDbType = SqlDbType.Int
            arrSqlParameter(1).Direction = ParameterDirection.Input
            arrSqlParameter(1).Value = objInformeCampoBE.Ciudad.Codigo

            arrSqlParameter(2) = New SqlParameter()
            arrSqlParameter(2).ParameterName = "@REFERENCIA"
            arrSqlParameter(2).SqlDbType = SqlDbType.VarChar
            arrSqlParameter(2).Size = 100
            arrSqlParameter(2).Direction = ParameterDirection.Input
            arrSqlParameter(2).Value = objInformeCampoBE.Referencia

            arrSqlParameter(3) = New SqlParameter()
            arrSqlParameter(3).ParameterName = "@CANT_HOSPEDAJES"
            arrSqlParameter(3).SqlDbType = SqlDbType.Int
            arrSqlParameter(3).Direction = ParameterDirection.Input
            arrSqlParameter(3).Value = objInformeCampoBE.CantidadHospedajes

            arrSqlParameter(4) = New SqlParameter()
            arrSqlParameter(4).ParameterName = "@CANT_RESTAURANTES"
            arrSqlParameter(4).SqlDbType = SqlDbType.Int
            arrSqlParameter(4).Direction = ParameterDirection.Input
            arrSqlParameter(4).Value = objInformeCampoBE.CantidadRestaurantes

            arrSqlParameter(5) = New SqlParameter()
            arrSqlParameter(5).ParameterName = "@TRANSPORTE"
            arrSqlParameter(5).SqlDbType = SqlDbType.VarChar
            arrSqlParameter(5).Size = 50
            arrSqlParameter(5).Direction = ParameterDirection.Input
            arrSqlParameter(5).Value = objInformeCampoBE.Transporte

            arrSqlParameter(6) = New SqlParameter()
            arrSqlParameter(6).ParameterName = "@OBSERVACIONES"
            arrSqlParameter(6).SqlDbType = SqlDbType.VarChar
            arrSqlParameter(6).Size = 100
            arrSqlParameter(6).Direction = ParameterDirection.Input
            arrSqlParameter(6).Value = objInformeCampoBE.Observaciones

            arrSqlParameter(7) = New SqlParameter()
            arrSqlParameter(7).ParameterName = "@ESTADO"
            arrSqlParameter(7).SqlDbType = SqlDbType.VarChar
            arrSqlParameter(7).Size = 1
            arrSqlParameter(7).Direction = ParameterDirection.Input
            arrSqlParameter(7).Value = objInformeCampoBE.Estado


            arrSqlParameter(8) = New SqlParameter()
            arrSqlParameter(8).ParameterName = "@USUARIO"
            arrSqlParameter(8).SqlDbType = SqlDbType.VarChar
            arrSqlParameter(8).Size = 10
            arrSqlParameter(8).Direction = ParameterDirection.Input
            arrSqlParameter(8).Value = objInformeCampoBE.Usuario

            arrSqlParameter(9) = New SqlParameter()
            arrSqlParameter(9).ParameterName = "@COD_INF_CAMPO"
            arrSqlParameter(9).SqlDbType = SqlDbType.Int
            arrSqlParameter(9).Direction = ParameterDirection.Output
            arrSqlParameter(9).Value = 0

            Dim oDatos As New AccesoDatos()

            oDatos.EjecutaSP("SP_InformeCampoIns", bExito, sMensaje, arrSqlParameter)

            Dim iCodInformeCampo As Integer

            If bExito Then

                iCodInformeCampo = oDatos.Parametro("@COD_INF_CAMPO")

                For Each objDetalleInformeCampo As DetalleInformeCampoBE In objInformeCampoBE.ListaDetalle
                    If Not InsertarDetalleInformeCampo(iCodInformeCampo, objDetalleInformeCampo, bExito, sMensaje) Then
                        Exit For
                    End If
                Next
            End If

            Return bExito

        Catch ex As Exception
            Return False
        End Try

    End Function

    Private Function InsertarDetalleInformeCampo(ByVal codigoInformeCampo As Integer, ByVal objDetalleInformeCampoBE As DetalleInformeCampoBE, ByRef bExito As Boolean, ByRef sMensaje As String) As Boolean

        Dim arrSqlParameter(2) As SqlParameter

        Try
            arrSqlParameter(0) = New SqlParameter()
            arrSqlParameter(0).ParameterName = "@COD_INF_CAMPO"
            arrSqlParameter(0).SqlDbType = SqlDbType.Int
            arrSqlParameter(0).Direction = ParameterDirection.Input
            arrSqlParameter(0).Value = codigoInformeCampo

            arrSqlParameter(1) = New SqlParameter()
            arrSqlParameter(1).ParameterName = "@TIPO_DETALLE"
            arrSqlParameter(1).SqlDbType = SqlDbType.Int
            arrSqlParameter(1).Direction = ParameterDirection.Input
            arrSqlParameter(1).Value = objDetalleInformeCampoBE.TipoDetalle

            arrSqlParameter(2) = New SqlParameter()
            arrSqlParameter(2).ParameterName = "@DESCRIPCION"
            arrSqlParameter(2).SqlDbType = SqlDbType.VarChar
            arrSqlParameter(2).Size = 50
            arrSqlParameter(2).Direction = ParameterDirection.Input
            arrSqlParameter(2).Value = objDetalleInformeCampoBE.Descripcion

            Dim oDatos As New AccesoDatos()

            oDatos.EjecutaSP("SP_DetInformeCampoIns", bExito, sMensaje, arrSqlParameter)

            Return bExito

        Catch ex As Exception
            Throw
        End Try


    End Function

    Public Function ListarInformeCampo(ByVal objInformeCampoIn As InformeCampoBE) As List(Of InformeCampoBE)

        Dim sqlListar As String
        Dim objListaInformeCampo As New List(Of InformeCampoBE)
        Dim objInformeCampoOut As InformeCampoBE = Nothing
        Dim arrSqlParameter(3) As SqlParameter
        Dim ds As DataSet
        Dim dt As DataTable

        Dim objDatos As New AccesoDatos()
        Dim blnExito As Boolean
        Dim strMensaje As String = String.Empty
        Dim intI As Integer = 0

        Try

            sqlListar = "SP_InformeCampoList"

            arrSqlParameter(0) = New SqlParameter("@USUARIO", System.Data.SqlDbType.VarChar)
            arrSqlParameter(0).Value = objInformeCampoIn.Usuario


            arrSqlParameter(1) = New SqlParameter("@ESTADO", System.Data.SqlDbType.VarChar)
            arrSqlParameter(1).Value = objInformeCampoIn.Estado

            arrSqlParameter(2) = New SqlParameter("@FECHAINICIO", System.Data.SqlDbType.VarChar)
            arrSqlParameter(2).Value = objInformeCampoIn.FechaInicio

            arrSqlParameter(3) = New SqlParameter("@FECHAFIN", System.Data.SqlDbType.VarChar)
            arrSqlParameter(3).Value = objInformeCampoIn.FechaFin


            ds = objDatos.DevuelveDataSet(sqlListar, blnExito, strMensaje, arrSqlParameter)
            dt = ds.Tables(0)


            objListaInformeCampo = New List(Of InformeCampoBE)()

            If dt.Rows.Count > 0 Then

                While intI < dt.Rows.Count
                    objInformeCampoOut = New InformeCampoBE()
                    objInformeCampoOut.Codigo = dt.Rows(intI).ItemArray(0).ToString
                    objInformeCampoOut.Usuario = dt.Rows(intI).ItemArray(1).ToString
                    objInformeCampoOut.FechaCreacion = dt.Rows(intI).ItemArray(2).ToString
                    objInformeCampoOut.Ciudad.Pais.Descripcion = dt.Rows(intI).ItemArray(3).ToString
                    objInformeCampoOut.Ciudad.Descripcion = dt.Rows(intI).ItemArray(4).ToString
                    objInformeCampoOut.Estado = dt.Rows(intI).ItemArray(5).ToString
                    intI = intI + 1

                    objListaInformeCampo.Add(objInformeCampoOut)
                End While
            End If

        Catch ex As Exception
            MsgBox(ex.Message, vbCritical, "Error del sistema")
            Return Nothing
            Exit Function
        End Try

        Return objListaInformeCampo
    End Function

    Public Function InformeCampoVer(ByVal codigo As Integer, ByRef bExito As Boolean, ByRef sMensaje As String) As InformeCampoBE

        Dim sqlVer As String
        Dim objInformeCampo As InformeCampoBE = Nothing
        Dim arrSqlParameter(0) As SqlParameter
        Dim ds As DataSet
        Dim dt As DataTable

        Dim objDatos As New AccesoDatos()

        Try
            sqlVer = "SP_InformeCampo_Ver"

            arrSqlParameter(0) = New SqlParameter("@CODIGO", System.Data.SqlDbType.Int)
            arrSqlParameter(0).Value = codigo

            ds = objDatos.DevuelveDataSet(sqlVer, bExito, sMensaje, arrSqlParameter)
            dt = ds.Tables(0)

            If dt.Rows.Count > 0 Then
                objInformeCampo = New InformeCampoBE()
                objInformeCampo.Codigo = dt.Rows(0).ItemArray(0).ToString
                objInformeCampo.Supervisor = dt.Rows(0).ItemArray(1).ToString
                objInformeCampo.Ciudad.Codigo = dt.Rows(0).ItemArray(2).ToString
                objInformeCampo.Ciudad.Pais.Codigo = dt.Rows(0).ItemArray(3).ToString
                objInformeCampo.Referencia = dt.Rows(0).ItemArray(4).ToString
                objInformeCampo.CantidadHospedajes = dt.Rows(0).ItemArray(5).ToString
                objInformeCampo.CantidadRestaurantes = dt.Rows(0).ItemArray(6).ToString
                objInformeCampo.Transporte = dt.Rows(0).ItemArray(7).ToString
                objInformeCampo.Observaciones = dt.Rows(0).ItemArray(8).ToString

                objInformeCampo.ListaDetalle = DetalleInformeCampoVer(codigo, bExito, sMensaje)

            End If

        Catch ex As Exception
            bExito = False
            sMensaje = ex.Message
            Return Nothing
            Exit Function
        End Try

        Return objInformeCampo
    End Function

    Public Function DetalleInformeCampoVer(ByVal codigo As Integer, ByRef bExito As Boolean, ByRef sMensaje As String) As List(Of DetalleInformeCampoBE)
        Dim sqlListar As String
        Dim listaDetalleInformeCampo As New List(Of DetalleInformeCampoBE)
        Dim objDetalleInformeCampoBE As DetalleInformeCampoBE = Nothing
        Dim arrSqlParameter(0) As SqlParameter
        Dim ds As DataSet
        Dim dt As DataTable

        Dim objDatos As New AccesoDatos()
        Dim intI As Integer = 0

        Try

            sqlListar = "SP_DetalleInformeCampo_Ver"

            arrSqlParameter(0) = New SqlParameter("@CODIGO", System.Data.SqlDbType.Int)
            arrSqlParameter(0).Value = codigo

            ds = objDatos.DevuelveDataSet(sqlListar, bExito, sMensaje, arrSqlParameter)
            dt = ds.Tables(0)


            listaDetalleInformeCampo = New List(Of DetalleInformeCampoBE)()

            If dt.Rows.Count > 0 Then

                While intI < dt.Rows.Count
                    objDetalleInformeCampoBE = New DetalleInformeCampoBE()
                    objDetalleInformeCampoBE.Codigo = dt.Rows(intI).ItemArray(0)
                    objDetalleInformeCampoBE.TipoDetalle = dt.Rows(intI).ItemArray(1).ToString
                    objDetalleInformeCampoBE.Descripcion = dt.Rows(intI).ItemArray(2).ToString

                    intI = intI + 1

                    listaDetalleInformeCampo.Add(objDetalleInformeCampoBE)
                End While
            End If

        Catch ex As Exception
            bExito = False
            sMensaje = ex.Message
            Return Nothing
            Exit Function
        End Try

        Return listaDetalleInformeCampo
    End Function

    Public Function ActualizarInformeCampo(ByVal objInformeCampo As InformeCampoBE, ByRef bExito As Boolean, ByRef sMensaje As String) As Boolean

        Dim arrSqlParameter(9) As SqlParameter

        Try

            arrSqlParameter(0) = New SqlParameter()
            arrSqlParameter(0).ParameterName = "@COD_INF_CAMPO"
            arrSqlParameter(0).SqlDbType = SqlDbType.Int
            arrSqlParameter(0).Direction = ParameterDirection.Input
            arrSqlParameter(0).Value = objInformeCampo.Codigo

            arrSqlParameter(1) = New SqlParameter()
            arrSqlParameter(1).ParameterName = "@SUPERVISOR"
            arrSqlParameter(1).SqlDbType = SqlDbType.VarChar
            arrSqlParameter(1).Size = 50
            arrSqlParameter(1).Direction = ParameterDirection.Input
            arrSqlParameter(1).Value = objInformeCampo.Supervisor

            arrSqlParameter(2) = New SqlParameter()
            arrSqlParameter(2).ParameterName = "@COD_CIUDAD"
            arrSqlParameter(2).SqlDbType = SqlDbType.Int
            arrSqlParameter(2).Direction = ParameterDirection.Input
            arrSqlParameter(2).Value = objInformeCampo.Ciudad.Codigo

            arrSqlParameter(3) = New SqlParameter()
            arrSqlParameter(3).ParameterName = "@REFERENCIA"
            arrSqlParameter(3).SqlDbType = SqlDbType.VarChar
            arrSqlParameter(3).Size = 100
            arrSqlParameter(3).Direction = ParameterDirection.Input
            arrSqlParameter(3).Value = objInformeCampo.Referencia

            arrSqlParameter(4) = New SqlParameter()
            arrSqlParameter(4).ParameterName = "@CANT_HOSPEDAJES"
            arrSqlParameter(4).SqlDbType = SqlDbType.Int
            arrSqlParameter(4).Direction = ParameterDirection.Input
            arrSqlParameter(4).Value = objInformeCampo.CantidadHospedajes

            arrSqlParameter(5) = New SqlParameter()
            arrSqlParameter(5).ParameterName = "@CANT_RESTAURANTES"
            arrSqlParameter(5).SqlDbType = SqlDbType.Int
            arrSqlParameter(5).Direction = ParameterDirection.Input
            arrSqlParameter(5).Value = objInformeCampo.CantidadRestaurantes

            arrSqlParameter(6) = New SqlParameter()
            arrSqlParameter(6).ParameterName = "@TRANSPORTE"
            arrSqlParameter(6).SqlDbType = SqlDbType.VarChar
            arrSqlParameter(6).Size = 50
            arrSqlParameter(6).Direction = ParameterDirection.Input
            arrSqlParameter(6).Value = objInformeCampo.Transporte

            arrSqlParameter(7) = New SqlParameter()
            arrSqlParameter(7).ParameterName = "@OBSERVACIONES"
            arrSqlParameter(7).SqlDbType = SqlDbType.VarChar
            arrSqlParameter(7).Size = 100
            arrSqlParameter(7).Direction = ParameterDirection.Input
            arrSqlParameter(7).Value = objInformeCampo.Observaciones

            arrSqlParameter(8) = New SqlParameter()
            arrSqlParameter(8).ParameterName = "@ESTADO"
            arrSqlParameter(8).SqlDbType = SqlDbType.VarChar
            arrSqlParameter(8).Size = 1
            arrSqlParameter(8).Direction = ParameterDirection.Input
            arrSqlParameter(8).Value = objInformeCampo.Estado

            arrSqlParameter(9) = New SqlParameter()
            arrSqlParameter(9).ParameterName = "@USUARIO"
            arrSqlParameter(9).SqlDbType = SqlDbType.VarChar
            arrSqlParameter(9).Size = 10
            arrSqlParameter(9).Direction = ParameterDirection.Input
            arrSqlParameter(9).Value = objInformeCampo.Usuario

            Dim oDatos As New AccesoDatos()

            oDatos.EjecutaSP("SP_InformeCampoUpd", bExito, sMensaje, arrSqlParameter)

            If bExito Then
                For Each objDetalleInformeCampo As DetalleInformeCampoBE In objInformeCampo.ListaDetalle
                    'If Not ActualizarDetalleInformeCampo(objInformeCampo.Codigo, objDetalleInformeCampo, bExito, sMensaje) Then
                    If Not InsertarDetalleInformeCampo(objInformeCampo.Codigo, objDetalleInformeCampo, bExito, sMensaje) Then
                        Exit For
                    End If
                Next
            End If

            Return bExito

        Catch ex As Exception
            Return False
        End Try
    End Function

    Private Function ActualizarDetalleInformeCampo(ByVal codigoInforme As Integer, ByVal objDetalleInforme As DetalleInformeCampoBE, ByRef bExito As Boolean, ByRef sMensaje As String) As Boolean

        Dim arrSqlParameter(3) As SqlParameter

        Try

            arrSqlParameter(0) = New SqlParameter()
            arrSqlParameter(0).ParameterName = "@COD_INF_CAMPO"
            arrSqlParameter(0).SqlDbType = SqlDbType.Int
            arrSqlParameter(0).Direction = ParameterDirection.Input
            arrSqlParameter(0).Value = codigoInforme

            arrSqlParameter(1) = New SqlParameter()
            arrSqlParameter(1).ParameterName = "@DET_INF_CAMPO"
            arrSqlParameter(1).SqlDbType = SqlDbType.Int
            arrSqlParameter(1).Direction = ParameterDirection.Input
            arrSqlParameter(1).Value = objDetalleInforme.Codigo

            arrSqlParameter(2) = New SqlParameter()
            arrSqlParameter(2).ParameterName = "@TIPO_DETALLE"
            arrSqlParameter(2).SqlDbType = SqlDbType.Int
            arrSqlParameter(2).Direction = ParameterDirection.Input
            arrSqlParameter(2).Value = objDetalleInforme.TipoDetalle

            arrSqlParameter(3) = New SqlParameter()
            arrSqlParameter(3).ParameterName = "@DESCRIPCION"
            arrSqlParameter(3).SqlDbType = SqlDbType.VarChar
            arrSqlParameter(3).Size = 50
            arrSqlParameter(3).Direction = ParameterDirection.Input
            arrSqlParameter(3).Value = objDetalleInforme.Descripcion

            Dim oDatos As New AccesoDatos()

            oDatos.EjecutaSP("SP_DetalleInformeCampoUpd", bExito, sMensaje, arrSqlParameter)

            Return bExito

        Catch ex As Exception

            Return False

        End Try

    End Function



End Class

